IF exists(SELECT * FROM dbo.sysobjects WHERE name='r_jaarLlnTotaal' AND xtype='FN') 
BEGIN
	DROP FUNCTION r_jaarLlnTotaal
END
GO

CREATE  function r_jaarLlnTotaal(@klas varchar(5))
returns @resultaat table(inschrijvingsNr char(7),
		vak char(2),
		DWprocent int,
		EXprocent int,
		TOTprocent int)
as
begin
  declare @hulpcijfers table(inschrijvingsNr char(7),vak char(2),
		DW float,DWmax float,
		EX float,EXmax float)
  insert into @hulpcijfers(inschrijvingsNr, vak, DW, DWmax, EX, EXmax)
  select c.inschrijvingsNr,'AT' as vak,
	round(c.DW,1),round(DWmax ,1),round(c.EX,1),round(EXmax,1)
  from r_trimestercijfers as c
	left join leerlingen..leerlingen as l
  	  on l.inschrijvingsNr = c.inschrijvingsNr
  where l.klas like @klas

  insert into @resultaat(inschrijvingsNr,vak,DWprocent,EXprocent,TOTprocent)
  select c.inschrijvingsNr,'AT',
       studiebegeleiding.dbo.r_procent(
		sum(case when c.DW >= 0   
                         then c.DW else 0 end),
		sum(case when c.DW >= 0 
			 then c.DWmax else 0 end)),
       studiebegeleiding.dbo.r_procent(
		sum(case when c.EX >= 0   
                         then c.EX else 0 end),
		sum(case when c.EX >= 0 
			 then c.EXmax else 0 end)),
       studiebegeleiding.dbo.r_totaal(
		sum(case when c.DW >= 0   
                         then c.DW else 0 end),
		sum(case when c.DW >= 0 
			 then c.DWmax else 0 end),
		sum(case when c.EX >= 0   
                         then c.EX else 0 end),
		sum(case when c.EX >= 0 
			 then c.EXmax else 0 end))
  from @hulpcijfers as c
  group by c.inschrijvingsNr
  return
end
---------